    use DB
    go
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProcedureB]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[StoredProcedureB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProcedureB]') AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'

    CREATE PROCEDURE [dbo].[StoredProcedureB]
       @Id bigint
    AS


    BEGIN

    SET NOCOUNT ON;
       
       SELECT  COUNT(DISTINCT o.Id)
          FROM
             Table1 n,
             Table2 o
          WHERE
             n.Id = @Id AND
             n.Id <> o.Id AND

             n.Key = o.Key
    END


    '
    END
    GO
    GRANT EXECUTE ON [dbo].[StoredProcedureB] TO [UserX]
    GO